from datetime import datetime

from django.core.paginator import EmptyPage, Paginator
from django.shortcuts import render
from rest_framework.views import APIView
# Create your views here.
from django.core.cache import cache
from django.http import JsonResponse
from django.utils import timezone
from .models import Post, Comments  # 确保导入正确的模型

# 发布评论接口
class PostCreateView(APIView):
    def post(self, request, *args, **kwargs):
        title = request.POST.get('title')
        content = request.POST.get('content')
        student_id = request.POST.get('student_id')

        # 检查所有必需的参数是否都存在
        if not all([title, content, student_id]):
            return JsonResponse({'error': 'Missing required fields.'}, status=400)

        # 创建时间可以使用当前时间
        created_at = timezone.now()

        # 尝试创建一个新的Post对象
        try:
            post = Post(title=title, content=content, created_at=created_at, student_id=student_id)
            post.save()
            return JsonResponse({'message': 'Post created successfully.'}, status=201)
        except Exception as e:
            return JsonResponse({'error': str(e)}, status=500)

    # def delete_comment(request, comment_id):
    # # 删除评论的逻辑
    #     if  comment
    #
    # # 返回响应





class StudentListView(APIView):
    def get(self, request, *args, **kwargs):
        """
        获取学生评论列表
        """
        student_id = request.GET.get('1')

        if not student_id:
            return JsonResponse({'error': 'Student ID is required.'}, status=400)
        cache_key = f'student_comments_{student_id}_{datetime.now().month}'
        # 获取当前月份
        current_month = datetime.now().month

        # 查询该学生本月的所有评论，按创建时间升序排序
        comments = Comments.objects.filter(
            student_id=student_id,
            create_time__month=current_month
        ).order_by('create_time')

        # 将查询结果转换为字典列表
        comments_list = [
            {
                'id': comment.id,
                'title': comment.title,
                'content': comment.content,
                'create_time': comment.create_time.strftime('%Y-%m-%d %H:%M:%S')
            }
            for comment in comments
        ]
        cache.set(cache_key, comments_list, timeout=60*12)#12分钟
        return JsonResponse({'comments': comments_list}, status=200)

from django.db.models import OuterRef, Subquery
from django.db.models.functions import RowNumber
from django.db.models import Q
from django.db import connection

class CommentListView(APIView):
    def get(self, request):
        page = int(request.GET.get('page', 1))  # 默认为第一页

        # 计算7天前的时间戳
        seven_days_ago = timezone.now() - timezone.timedelta(days=7)

        # 查询标题包含“好评”的评论，并且创建时间在过去7天内
        comments = Comments.objects.filter(title__icontains='好评', create_time__gte=seven_days_ago).order_by(
                    '-create_time')

        # 创建分页器对象
        paginator = Paginator(comments, 8)  # 每页显示8条评论

        # 获取当前页的评论
        try:
            comments_page = paginator.page(page)
        except EmptyPage:
                    # 如果页数超出范围，返回空列表
             comments_page = []

             # 将查询结果转换为字典列表
        comments_list = []
        for comment in comments_page:
            comments_list.append({
                            'id': comment.id,
                            'title': comment.title,
                            'content': comment.content,
                            'create_time': comment.create_time.strftime('%Y-%m-%d %H:%M:%S')
                        })

            # 返回JSON响应
        return JsonResponse({'comments':comments_list,'total_pages': paginator.num_pages,'current_page': page}, status=200)

    def get_student_recent_comments(student_id):
        # 使用Q对象构建复杂的查询条件
        query = Q(content__icontains="红包") | Q(content__icontains="微信")

        # 构建原生SQL查询
        sql = """
            SELECT *
            FROM (
                SELECT *,
                       ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY create_time DESC) AS row_num
                FROM {table}
                WHERE student_id = %s AND NOT ({query})
            ) t
            WHERE row_num <= 8;
        """.format(table=Comments._meta.db_table, query=str(query))

        # 执行原生SQL查询
        with connection.cursor() as cursor:
            cursor.execute(sql, [student_id])
            columns = [col[0] for col in cursor.description]
            results = [
                dict(zip(columns, row))
                for row in cursor.fetchall()
            ]

        return results
from django.db.models import Count
from django.views import View
from django.http import JsonResponse
from .models import Student, Comments

class StudentCommentCountView(View):
    def get(self, request):
        # 使用聚合函数Count来计算每个学生的评论数量
        students_with_comment_counts = Student.objects.annotate(
            comment_count=Count('comments', distinct=True)
        )

        # 将结果转换为字典列表
        result = [
            {
                'student_account': student.stu_name,
                'comment_count': student.comment_count,
            }
            for student in students_with_comment_counts
        ]

        # 返回JSON响应
        return JsonResponse(result, safe=False)
